set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Stored Procedure

ALTER PROCEDURE [dbo].[sprTongHopPhanTichDoanhSoQuyReport]
	-- Add the parameters for the stored procedure here
	@BranchID uniqueidentifier, 
	@Quarter INT,
	@Year INT,
	@BrandID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--DECLARE @vReportDate DATETIME
	--SELECT @vReportDate = dbo.Date(@Year, @Month, 1)

	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)

	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID

	DECLARE @BrandName NVARCHAR(50)
	DECLARE @BrandCode NVARCHAR(50)
	SELECT @BrandCode = BrandCode, @BrandName = BrandName FROM dbo.ThuongHieu WHERE BrandID = @BrandID

	CREATE TABLE #tblMonthlyRevenue
	(
		ID INT IDENTITY(1, 1)
		,[Month] INT
		,[Year] INT
		,[DoanhSoTD] MONEY
		,[DoanhSoCC] MONEY
		,KhachHangID UNIQUEIDENTIFIER
		,ThuongHieu nvarchar(100)
	)
	
	DECLARE @Thang1 INT
	DECLARE @Thang2 INT
	DECLARE @Thang3 INT
	
	SET @Thang1 = CASE @Quarter WHEN 1 THEN 1
								WHEN 2 then 4
								WHEN 3 THEN 7
								WHEN 4 THEN 10
					END
	SET @Thang2 = @Thang1 + 1
	SET @Thang3 = @Thang2 + 1

    -- Insert statements for procedure here
	CREATE TABLE #tblTongHopPhanTichDoanhSoQuyReport
	(
		TenTrungTam NVARCHAR(255),
		KhachHangID UNIQUEIDENTIFIER,
		MaKH NVARCHAR(100),
		TenKH NVARCHAR(100),
		LEVEL INT,
		ParentKhachHangID UNIQUEIDENTIFIER,
		Quy INT,
		Nam INT,
		MucChietKhauTD DECIMAL,
		MucChietKhauCC DECIMAL,
		TenThuongHieu NVARCHAR(10),
		DuThuongID UNIQUEIDENTIFIER,
		StartDate DATETIME,
		DoanhSoCamKet MONEY,
		DoanhSoTDThang1 MONEY,
		DoanhSoTDThang2 MONEY,
		DoanhSoTDThang3 MONEY,
		DoanhSoCCThang1 MONEY,
		DoanhSoCCThang2 MONEY,
		DoanhSoCCThang3 MONEY
	)
		INSERT INTO #tblMonthlyRevenue(KhachHangID, [Month], [Year], [DoanhSoTD], [DoanhSoCC], ThuongHieu)
		SELECT mr.KhachHangID
		, [Thang], [Nam]
		, DoanhSoTD
		, DoanhSoCC
		,mr.ThuongHieu
		FROM vwGetTotalMonthlyRevenue mr
		INNER JOIN ThuongHieu th ON th.BrandID = @BrandID
		WHERE mr.Nam = @Year
		AND mr.ThuongHieu = th.BrandCode

		DECLARE @KhachHangID UNIQUEIDENTIFIER
		DECLARE KhachHangCursor CURSOR FOR SELECT DISTINCT KhachHangID FROM #tblMonthlyRevenue
		Open KhachHangCursor

		FETCH NEXT FROM KhachHangCursor
		INTO @KhachHangID

		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @DoanhSoTDThang1 MONEY
			DECLARE @DoanhSoTDThang2 MONEY
			DECLARE @DoanhSoTDThang3 MONEY
			DECLARE @DoanhSoCCThang1 MONEY
			DECLARE @DoanhSoCCThang2 MONEY
			DECLARE @DoanhSoCCThang3 MONEY
			
			SELECT @DoanhSoTDThang1 = SUM(DoanhSoTD)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND [Month] = @Thang1

			SELECT @DoanhSoTDThang2 = SUM(DoanhSoTD)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND [Month] = @Thang2

			SELECT @DoanhSoTDThang3 = SUM(DoanhSoTD)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND [Month] = @Thang3

			SELECT @DoanhSoCCThang1 = SUM(DoanhSoCC)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND [Month] = @Thang1

			SELECT @DoanhSoCCThang2 = SUM(DoanhSoCC)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND [Month] = @Thang2

			SELECT @DoanhSoCCThang3 = SUM(DoanhSoCC)
			FROM #tblMonthlyRevenue
			WHERE KhachHangID = @KhachHangID
			AND [Month] = @Thang3

			
			INSERT INTO #tblTongHopPhanTichDoanhSoQuyReport(TenTrungTam, KhachHangID, MaKH, TenKH, LEVEL, ParentKhachHangID, Nam, TenThuongHieu,
															DoanhSoTDThang1 ,
															DoanhSoTDThang2,
															DoanhSoTDThang3,
															DoanhSoCCThang1,
															DoanhSoCCThang2,
															DoanhSoCCThang3)
			SELECT @TrungTamName AS TenTrungTam, D.KhachHangID, D.MaKH, D.TenKH, D.Level, D.ParentID AS ParentKhachHangID, @Year AS Nam, @BrandName AS TenThuongHieu,
															ISNULL(@DoanhSoTDThang1, 0) ,
															ISNULL(@DoanhSoTDThang2, 0),
															ISNULL(@DoanhSoTDThang3,0) ,
															ISNULL(@DoanhSoCCThang1,0) ,
															ISNULL(@DoanhSoCCThang2,0) ,
															ISNULL(@DoanhSoCCThang3,0)
			FROM vwAllCustomers D 
			WHERE d.KhachhangID = @KhachHangID
			
			FETCH NEXT FROM KhachHangCursor
			INTO @KhachHangID
		END

	--SELECT * FROM #tblMonthlyRevenue
	SELECT * FROM #tblTongHopPhanTichDoanhSoQuyReport

	CLOSE KhachHangCursor;
	DEALLOCATE KhachHangCursor;
	DROP TABLE #tblTongHopPhanTichDoanhSoQuyReport
	DROP TABLE #tblMonthlyRevenue

	-- exec sprTongHopPhanTichDoanhSoQuyReport '0A110906-E0A3-46AB-B716-4F56085945D5', 2, 2010, 1
END




